package lk.icta.core;

import lk.icta.common.Event;
import lk.icta.common.dao.PreloadableDAO;
import lk.icta.common.entity.*;
import lk.icta.common.util.RolePermissionUtils;
import lk.icta.core.entity.*;
import lk.icta.user.entity.Role;
import lk.icta.user.entity.User;
import org.hibernate.cfg.AnnotationConfiguration;
import org.hibernate.tool.hbm2ddl.SchemaExport;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.BeansException;
import org.springframework.context.ApplicationContext;
import org.springframework.context.ApplicationContextAware;
import org.springframework.core.io.ClassPathResource;
import org.springframework.core.io.FileSystemResource;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.orm.jpa.EntityManagerFactoryInfo;
import org.springframework.test.jdbc.JdbcTestUtils;

import javax.sql.DataSource;
import java.io.File;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * @author Mahesha Kalpanie
 */
public class DBInit implements ApplicationContextAware {
    public static final String USE_NW_DERBY = "nwderby";
    public static final String RECREATE_DB = "recreatedb";
    // public static final String INSERT_GN_DIVISIONS = "allGn";
    private static final Logger logger = LoggerFactory.getLogger(DBInit.class);

    private DataSource dataSource;
    private boolean createCleanDB = false;

    private static final List<Class> entityClasses = new ArrayList<Class>();

    static {
        entityClasses.add(Province.class);
        entityClasses.add(District.class);
        entityClasses.add(DSDivision.class);
        entityClasses.add(GNDivision.class);
        entityClasses.add(WorkflowItem.class);

        entityClasses.add(User.class);
        entityClasses.add(Role.class);
        entityClasses.add(Child.class);
        entityClasses.add(Contacts.class);
        entityClasses.add(Document.class);
        entityClasses.add(DocumentCategory.class);
        entityClasses.add(Person.class);
        entityClasses.add(Occupier.class);
        entityClasses.add(President.class);
        entityClasses.add(RegisteredPost.class);

        entityClasses.add(Alienation.class);
        entityClasses.add(AlienationMethod.class);
        entityClasses.add(GeologicalType.class);
        entityClasses.add(BoundaryType.class);
        entityClasses.add(Land.class);
        entityClasses.add(LandApplication.class);
        entityClasses.add(Holder.class);
        entityClasses.add(LandUsage.class);
        entityClasses.add(LocalAuthorityType.class);
        entityClasses.add(Schedule.class);
        entityClasses.add(LeaseDetail.class);
        entityClasses.add(Mortgage.class);
        entityClasses.add(Organization.class);
        entityClasses.add(LandAssociation.class);
        entityClasses.add(URMGOIUser.class);
        entityClasses.add(CalenderSchedule.class);
        entityClasses.add(GOInterestInformation.class);
        entityClasses.add(AppointmentSchedule.class);
        entityClasses.add(GeoParcelCode.class);
        entityClasses.add(Objection.class);
        entityClasses.add(Kachchery.class);

        entityClasses.add(LocalAuthority.class);
        entityClasses.add(President.class);

        entityClasses.add(Event.class);

        entityClasses.add(LandRailway.class);
        entityClasses.add(Branch.class);
        entityClasses.add(DistrictEngineerZone.class);
        entityClasses.add(IPWZone.class);
        entityClasses.add(LandLocation.class);
        entityClasses.add(RailwayStation.class);
        entityClasses.add(RailwayProtectionZone.class);
        entityClasses.add(RailwayTrack.class);

        entityClasses.add(AlienationRailway.class);
        entityClasses.add(Valuation.class);
        entityClasses.add(Payment.class);
        entityClasses.add(Taxation.class);
        entityClasses.add(ArrearsHistoryRecord.class);

        entityClasses.add(Institute.class);

    }

    public void setDataSource(DataSource dataSource) {
        this.dataSource = dataSource;
    }

    public void setCreateCleanDB(boolean createCleanDB) {
        this.createCleanDB = createCleanDB;
    }

    public void setApplicationContext(ApplicationContext ctx) throws BeansException {
        logger.info("Starting the database initialization..");

        logger.debug("Create clean db : {}", createCleanDB); //false

        boolean mysql = false;

        // detect the target DB
        EntityManagerFactoryInfo emf = (EntityManagerFactoryInfo) ctx.getBean("entityManagerFactory");
        if ("org.hibernate.dialect.MySQLDialect".equals(emf.getPersistenceUnitInfo().getProperties().
                getProperty("hibernate.dialect"))) {
            mysql = true;
            System.setProperty("elanddb.mysql", "true");
            logger.debug("Detected MySQL as the target database");
        }

        //todo : if not my sql then do the following for creating test database
        // decide if DB is to be recreated
        boolean recreateDb = false;
        if (Boolean.getBoolean(USE_NW_DERBY)) {
            logger.debug("Networked Derby selected as the target database..");
            try {
                //JdbcTestUtils.countRowsInTable(new NamedParameterJdbcTemplate(dataSource), "COMMON_RAIL.Province");
            } catch (Exception ignore) {
                recreateDb = true;
            }
        } else {
            recreateDb = !mysql || createCleanDB;    // recreate for derby or for MySQL when explicitly requested
        }
        // if (recreateDb || Boolean.getBoolean("recreatedb")) {
        if (recreateDb) {
            //cretae new database (derby or mysql) in development server based on the specified param in spring.xml
            logger.info("Recreating a new database..");
            recreateCleanDB(mysql);
            // perform additional initialization with Java code
            //TODO: uncomment
            RolePermissionUtils.setPermissionBits(ctx);
            //   additionalInitialization(ctx);
        } else if (Boolean.getBoolean(RECREATE_DB)) {
            //TODO: remove this
            //cretae db in mysql live server with specifying the parameter
            logger.info("Recreating a new database..");
            recreateCleanDB(mysql);
            RolePermissionUtils.setPermissionBits(ctx);

        }/* else {
            //set role permission in newly created mysql live server after manually executing the insert scripts
            RolePermissionUtils.setPermissionBits(ctx);   //todo: update permissions only after creating DB
        } */

        Map<String, PreloadableDAO> preloadableDaos = ctx.getBeansOfType(PreloadableDAO.class);
        for (PreloadableDAO dao : preloadableDaos.values()) {
            dao.preload();
        }
        logger.info("Pre-loaded master tables ... Application initialized!");
    }

    private void recreateCleanDB(boolean mysql) {
        System.out.println("\n**********          **********          **********          **********          **********\n");
        logger.info("Re-creating a clean Database ..." +mysql);

        //todo: do not drop db if exiting
        // drop mysql databases if they exist
        if (mysql) {
            try {
                Statement s = dataSource.getConnection().createStatement();
                s.execute("SET FOREIGN_KEY_CHECKS=0");
                s.execute("DROP DATABASE IF EXISTS `COMMON_RAIL`;");
                s.execute("DROP DATABASE IF EXISTS `USERS_RAIL`;");
                s.execute("DROP DATABASE IF EXISTS `ELAND_RAIL`;");
                s.execute("SET FOREIGN_KEY_CHECKS=1");
                s.close();

            } catch (Exception ignore) {
                logger.warn("Exception while dropping existing MySQL databases", ignore);
            }
        }

        // create schemas (derby) or databases (mysql) -
        if (mysql) {
            try {
                JdbcTestUtils.executeSqlScript(new JdbcTemplate(dataSource),
                        new ClassPathResource("database/create_mysql_databases.sql"), false);
                logger.info("Created MySQL databases : COMMON_RAIL, USERS_RAIL, ELAND_RAIL");
            } catch (Exception e) {
                logger.error("Error creating MySQL databases - COMMON_RAIL, USERS_RAIL", e);
                throw new IllegalStateException("Could not create MySQL databases. See log for details", e);
            }
        } else {
            try {
                logger.debug("creating derby schema");
                JdbcTestUtils.executeSqlScript(new JdbcTemplate(dataSource),
                        new ClassPathResource("database/create_schemas.sql"), false);
                logger.info("Created the schemas : COMMON_RAIL, USERS_RAIL");
            } catch (Exception ignore) {
                ignore.printStackTrace();
            }
        }

        // generate schema creation and drop script
        String[] fileName = null;
        if (mysql) {
            fileName = generateSchemaFromHibernate(Dialect.MYSQL);
        } else {
            fileName = generateSchemaFromHibernate(Dialect.DERBY);
        }

        // drop tables if any exist
        //  if (!mysql) {
        try {
            JdbcTestUtils.executeSqlScript(new JdbcTemplate(dataSource),
                    new FileSystemResource(fileName[1]), false);
            logger.info("Drop existing tables using generated script : " + fileName[1]);
        } catch (Exception e) {
            logger.debug("Exception while dropping existing tables using script : " + fileName[1]);
        }
        //   }

        // create tables
        try {
            JdbcTestUtils.executeSqlScript(new JdbcTemplate(dataSource),
                    new FileSystemResource(fileName[0]), false);
            logger.info("Created tables using generated script : " + fileName[0]);

        } catch (Exception e) {
            logger.error("Error creating tables", e);
            throw new IllegalStateException("Could not create tables. See log for details", e);
        }

        try {

            //populate demo data
/*
            logger.info("Trying to populate sample data");
            SimpleJdbcTestUtils.executeSqlScript(new SimpleJdbcTemplate(dataSource),
                new ClassPathResource("database/demo_roles.sql"), false);
            logger.info("Populated the tables with sample data from : populate_sample_data.sql");
             */

// populate with sample data


            /*SimpleJdbcTestUtils.executeSqlScript(new SimpleJdbcTemplate(dataSource),
       new ClassPathResource("database/populate_sample_data.sql"), false);   */


            if (mysql) {
                // if specified in spring.xml
                // if (createCleanDB) {
                logger.info("Trying to populate sample data");
                JdbcTestUtils.executeSqlScript(new JdbcTemplate(dataSource),
                        new ClassPathResource("database/populate_sample_data.sql"), false);
                logger.info("Populated the tables with sample data from : populate_sample_data.sql");
                logger.info("Populating GN Divisions");
               // JdbcTestUtils.executeSqlScript(new JdbcTemplate(dataSource)
               //         , new ClassPathResource("database/gn_divisions_sample.sql"), false);
                logger.debug("Populated GN divisions with sample data");
                //gn_divisions_sample.sql
                //}

            } /*else {
                //if derby (for unit tests)
                logger.info("Trying to populate sample data");
                SimpleJdbcTestUtils.executeSqlScript(new SimpleJdbcTemplate(dataSource),
                    new ClassPathResource("database/populate_sample_data.sql"), false);
                logger.info("Populated the tables with sample data from : populate_sample_data.sql");
                logger.info("polulating GN Divisions");
                SimpleJdbcTestUtils.executeSqlScript(new SimpleJdbcTemplate(dataSource)
                    , new ClassPathResource("database/gn_divisions_sample.sql"), false);
                logger.debug("populated GN divisions with sample data form gn_divisions_sample.sql");
            } */

            //populate sample GN Divisions
            /* if (mysql || Boolean.getBoolean(INSERT_GN_DIVISIONS)) {
                logger.info("polulating GN Divisions");
                SimpleJdbcTestUtils.executeSqlScript(new SimpleJdbcTemplate(dataSource)
                    , new ClassPathResource("database/populate_sample_gn_division.sql"), false);
                logger.debug("populated GN divisions with sample data form populate_sample_gn_division.sql");
            } else if (mysql || !Boolean.getBoolean(INSERT_GN_DIVISIONS)) {
                SimpleJdbcTestUtils.executeSqlScript(new SimpleJdbcTemplate(dataSource)
                    , new ClassPathResource("database/populate_sample_gn_division_limited.sql"), false);
                logger.debug("populated limited  GN divisions with sample data form populate_sample_gn_division_limited.sql");
            }*/

        } catch (Exception e) {
            logger.error("Error populating the database with initial data", e);
            throw new IllegalStateException("Could not initialize the database. See log for details", e);
        }
    }

    // for testing

    public static void main(String[] args) {
        new DBInit().generateSchemaFromHibernate(Dialect.DERBY);
        new DBInit().generateSchemaFromHibernate(Dialect.MYSQL);
    }

    private String[] generateSchemaFromHibernate(Dialect dialect) {
        AnnotationConfiguration cfg = new AnnotationConfiguration();
        cfg.setProperty("hibernate.hbm2ddl.auto", "create");

        for (Class<Object> clazz : entityClasses) {
            cfg.addAnnotatedClass(clazz);
        }
        if (Dialect.MYSQL.equals(dialect)) {
            cfg.setProperty("hibernate.dialect", "org.hibernate.dialect.MySQL5InnoDBDialect");
        } else {
            cfg.setProperty("hibernate.dialect", dialect.getDialectClass());
        }

        SchemaExport export = new SchemaExport(cfg);
        export.setDelimiter(";");
        export.setFormat(true);

        String[] fileName = new String[2];
        fileName[0] = System.getProperty("java.io.tmpdir") + File.separator + "create_" + dialect.name().toLowerCase() + ".sql";
        export.setOutputFile(fileName[0]);
        export.execute(false, false, false, true /* Set to false to create drop table DDL*/);

        fileName[1] = System.getProperty("java.io.tmpdir") + File.separator + "drop_" + dialect.name().toLowerCase() + ".sql";
        export.setOutputFile(fileName[1]);
        export.execute(false, false, true, false);
        return fileName;
    }

    private static enum Dialect {
        DERBY("org.hibernate.dialect.DerbyDialect"),
        MYSQL("org.hibernate.dialect.MySQLDialect");

        private String dialectClass;

        private Dialect(String dialectClass) {
            this.dialectClass = dialectClass;
        }

        public String getDialectClass() {
            return dialectClass;
        }
    }
}